﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace Library
{
    public class DataAccess
    {
       
        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        public static void execute(string sql)
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
            }
        }

        /// <summary>
        /// 切分传进来的ArrayList，处理得到的sql语句。成功时返回true，失败返回false
        /// </summary>
        /// <param name="sqlList"></param>
        public static bool execute(ArrayList sqlList)
        {
            bool flag = false;
            using (SqlConnection conn = new SqlConnection(SqlHelper.connStr))
            {
                conn.Open();
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (String sql in sqlList)
                        {
                            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql);
                        }

                        //提交事务
                        trans.Commit();
                        flag = true;
                    }
                    catch
                    {
                        trans.Rollback();
                        flag = false;
                    }
                    finally { }
                }
            }
            return flag;
        }

        /// <summary>
        /// 根据sql语句返回object
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object getObject(string sql)
        {
            object obj = SqlHelper.ExecuteScalar(SqlHelper.connStr, CommandType.Text, sql);
            return obj;
        }

        /// <summary>
        /// 根据sql语句返回SqlDataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static SqlDataReader getSqlDataReader(string sql)
        {
            SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.connStr, CommandType.Text, sql);
            return dr;
        }


        /// <summary>
        /// 根据sql语句返回DataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable getDataTable(string sql)
        {
            DataTable dt = SqlHelper.ExecuteDataset(SqlHelper.connStr, CommandType.Text, sql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 根据sql语句返回DataSet
        /// </summary>
        ///<param name="sql"></param>
        /// <returns></returns>
        public static DataSet getDataSet(string sql)
        {
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.connStr, CommandType.Text, sql);
            return ds;
        }

  
        /// <summary>
        /// 执行存储过程返回DataSet
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="storedParams"></param>
        /// <returns></returns>
        public static DataSet getDataSetByProc(string ProcName, params SqlParameter[] storedParams)
        {
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.connStr, CommandType.StoredProcedure, ProcName, storedParams);
            return ds;
        }

    }
}
